﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GlxRRSDB;
using GlxRRSDB.ReportDataSetTableAdapters;
using GlxRRSDB.Data;
using System.Data.SqlClient;
using System.Data;
using GlxRRSDB.Utils;

namespace GlxRRSDB.Facades
{
    public class TicketReportFacade
    {
        #region MEMBER
        private static SqlConnection _connection = null;
        #endregion
        private static SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlConnection();
                    _connection.ConnectionString = GlxRRSDB.Properties.Settings.Default.ReportDBConnectionString;
                }
                return _connection;
            }
        }

        #region SQL COMMAND
        private const string SQL_TICKET_REVENUE = "SELECT * " +
                                                "FROM tblTicketRevenue " +
                                                "WHERE (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)";
        private const string SQL_DETAIL_FILM_ADMISSION = "SELECT FilmID, Admission, Date, FilmTitle " +
                                   "FROM FilmFilmRevenueView " +
                                   "WHERE CinemaID=@cinemaid AND (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY FilmID ASC, DATE ASC";
        private const string SQL_DETAIL_FILM_BO = "SELECT FilmID, BO, Date, FilmTitle " +
                                   "FROM FilmFilmRevenueView " +
                                   "WHERE CinemaID=@cinemaid AND (DATEDIFF(day, (@fromdate), DATE) >= 0) AND (DATEDIFF(day, (@todate), DATE) <= 0)" +
                                   "ORDER BY FilmID ASC, DATE ASC";
        #endregion

        public static List<ReportDataSet.tblTicketRevenueRow> getTicketRevenue(DateTime date, int cinemaId)
        {
            tblTicketRevenueTableAdapter adapter = new tblTicketRevenueTableAdapter();
            return adapter.GetTicketRevenueByRange(date.StartOfWeek(DayOfWeek.Friday), date.EndOfWeek(DayOfWeek.Friday), cinemaId).ToList();
        }

        public static List<ReportDataSet.tblCORevenueRow> getCORevenue(DateTime date, int cinemaId)
        {
            tblCORevenueTableAdapter adapter = new tblCORevenueTableAdapter();
            return adapter.GetCORevenueByRange(date.StartOfWeek(DayOfWeek.Friday), date.EndOfWeek(DayOfWeek.Friday), cinemaId).ToList();
        }
        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailFilmAdmissionWeek(DateTime fromdate, int cinemaId, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_FILM_ADMISSION);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = cinemaId;
            cmd.Parameters[1].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[2].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int filmId = reader.GetInt32(0);
                    int admission = reader.GetInt32(1);
                    DateTime date = reader.GetDateTime(2);
                    string filmTitle = reader.GetString(3);
                    //
                    if (dict.ContainsKey(filmId))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[filmId];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.FilmTitle = filmTitle;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.FilmTitle = filmTitle;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(filmId, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
        public static Dictionary<int, Dictionary<DateTime, DailyReportData>> getDetailFilmBOWeek(DateTime fromdate, int cinemaId, Dictionary<int, Dictionary<DateTime, DailyReportData>> dict)
        {
            SqlDataReader reader;
            SqlCommand cmd = new SqlCommand(SQL_DETAIL_FILM_BO);
            cmd.Connection = Connection;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@cinemaid", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@fromdate", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@todate", SqlDbType.DateTime));
            //
            cmd.Parameters[0].Value = cinemaId;
            cmd.Parameters[1].Value = fromdate.StartOfWeek(DayOfWeek.Friday);
            cmd.Parameters[2].Value = fromdate.EndOfWeek(DayOfWeek.Friday);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int filmId = reader.GetInt32(0);
                    long admission = reader.GetInt64(1);
                    DateTime date = reader.GetDateTime(2);
                    string filmTitle = reader.GetString(3);
                    //
                    if (dict.ContainsKey(filmId))
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = dict[filmId];
                        if (dictDateTime.ContainsKey(date.Date))
                        {
                            DailyReportData data = dictDateTime[date.Date];
                            data.currentDate += admission;
                        }
                        else
                        {
                            DailyReportData data = new DailyReportData();
                            data.FilmTitle = filmTitle;
                            data.FromDate = date.Date;
                            data.currentDate = admission;
                            dictDateTime.Add(data.FromDate, data);
                        }
                    }
                    else
                    {
                        Dictionary<DateTime, DailyReportData> dictDateTime = new Dictionary<DateTime, DailyReportData>();
                        DailyReportData data = new DailyReportData();
                        data.FilmTitle = filmTitle;
                        data.FromDate = date.Date;
                        data.currentDate = admission;
                        dictDateTime.Add(data.FromDate, data);
                        //
                        dict.Add(filmId, dictDateTime);
                    }
                }
            }
            reader.Close();

            return dict;
        }
    }
}
